# _*_ coding: utf-8 _*_
import os
import time as pytime
import logging
import sys
from datetime import datetime, time, timedelta
from utils import zymysql
from collections import defaultdict
from concurrent.futures import ThreadPoolExecutor

# sys.path.append(os.path.abspath(os.path.join(os.path.dirname(__file__), "..")))

# 设置日志配置
current_dir = os.path.dirname(os.path.abspath(__file__))
target_dir = os.path.join(os.path.dirname(current_dir), "logs")  # 子目录名称
log_filename = os.path.join(target_dir, "attendance_record.log")
logging.basicConfig(
    level=logging.INFO,  # 设置日志级别为INFO
    format='%(asctime)s - %(levelname)s - %(message)s',  # 设置日志格式
    handlers=[
        logging.FileHandler(log_filename),  # 将日志写入文件
        logging.StreamHandler()  # 也打印到控制台
    ]
)


# 获取排班记录
def get_att_time(att_date):
    print(f"获取 {att_date} 的排班记录")
    sql = (
        "select u.userid userId, s.sch_date schDate, t.team_name teamName, "
        "t.time_number timeNumber , t.on_time onTime, t.off_time offTime "
        "from att_appuser u "
        "join att_membermapping m on u.id = m.appuser_id "
        "join att_schedule s on s.member_id = m.member_id "
        "join att_time t on t.team_id = s.team_id "
        "where s.sch_date = %s "
        "and s.team_id != 1000000000000000001"
    )
    midnight = datetime(year=att_date.year, month=att_date.month, day=att_date.day)
    timestamp = int(midnight.timestamp() * 1000)
    print(f"转换时间戳 {timestamp}")

    try:
        records = zymysql.seeyon_param_query(sql, timestamp)
    except Exception as e:
        print(f"操作出错: {e}")
        return False
    columns = ['user_id', 'sch_date', 'team_name', 'time_number', 'on_time', 'off_time']
    return [dict(zip(columns, record)) for record in records]


# 获取区间时间内打卡记录
def get_att_check_in_out_time(date_arr):
    print(f"获取 {date_arr} 的打卡记录")
    query = "SELECT USERID, CHECKTIME FROM checkinout WHERE CHECKTIME BETWEEN  %s and %s"
    print(f"获取 {date_arr} sql记录")
    records = zymysql.zkteco_query(query, date_arr)
    print(f"获取 {len(records)} 条sql记录")
    columns = ['user_id', 'check_time']
    return [dict(zip(columns, record)) for record in records]


# 根据时间获取凌晨04：00时间和次日10：00
def get_midnight_and_nextday(dt: datetime):
    midnight = datetime.combine(dt.date(), time(hour=4))
    next_day_10am = datetime.combine(dt.date() + timedelta(days=1), time(hour=10))
    return (midnight, next_day_10am)


# 根据时间获取昨日00：00时间当前
def get_yesterday_and_midnight(dt: datetime):
    start = datetime.combine(dt.date(), time(hour=8))
    if(dt.hour < 10):
        start = datetime.combine(dt.date() - timedelta(days=1), time(hour=4))
    return (start, dt)


# 筛选打卡记录
def filter_schedule_by_checkins(att_date_list, check_time_list):
    # 第一步：将 check_time_list 转为 user_id -> list 的映射
    b_map = defaultdict(list)
    for record in check_time_list:
        b_map[record['user_id']].append(record)

    # 第二步：遍历 check_time_list 的 map，查找 att_date_list 中 user_id 一致的对象

    result = []

    for user_id, checkins in b_map.items():
        # 获取当前 user_id 对应的排班记录
        schedules = [item for item in att_date_list if item['user_id'] == user_id]

        # 排班记录不足两条，全部打卡记录都返回
        if len(schedules) <= 1:
            result.extend(checkins)
            continue

        # 按 time_number 升序排序排班
        schedules.sort(key=lambda x: x['time_number'])

        # 转换 check_time 为绝对时间戳（毫秒）
        for checkin in checkins:
            dt = checkin['check_time']
            checkin['check_ts'] = int(dt.timestamp() * 1000)

        used_ids = set()

        for i in range(1, len(schedules)):
            prev = schedules[i - 1]
            curr = schedules[i]

            # 构造前一班的下班时间和当前班的上班时间（加上 sch_date）
            start_ts = prev['sch_date'] + prev['off_time']
            end_ts = curr['sch_date'] + curr['on_time']

            # 找出落在区间内的打卡记录
            in_range = []

            for idx, c in enumerate(checkins):
                if start_ts < c['check_ts'] < end_ts:
                    in_range.append((idx, c))
                    used_ids.add(idx)

            if in_range:
                in_range.sort(key=lambda x: x[1]['check_ts'])
                min_idx, min_rec = in_range[0]
                max_idx, max_rec = in_range[-1]

                result.append(min_rec)

                if max_rec['check_ts'] - min_rec['check_ts'] > 10 * 60 * 1000:
                    result.append(max_rec)
                    used_ids.add(max_idx)

        # 未落入任何范围的记录，全部保留
        for idx, checkin in enumerate(checkins):
            if idx not in used_ids:
                result.append(checkin)
    return result


# 批量检查记录是否存在
def batch_check_records_exist(records, date_arr):
    query = "SELECT USERID, CHECKTIME FROM checkinout_dwd WHERE CHECKTIME BETWEEN  %s and %s"
    print(f"获取 {date_arr} sql历史记录")
    old_records = zymysql.zkteco_query(query, date_arr)
    print(f"获取 {len(old_records)} 条sql历史记录")

    old_set = set((row[0], row[1]) for row in old_records)

    # 找出 records 中不存在于 old_records 的数据
    new_records = [
        record for record in records
        if (record['user_id'], record['check_time']) not in old_set
    ]

    print(f"待插入的新记录数量: {len(new_records)}")
    return new_records


def batch_insert_attendance_records(records, att_date):
    print("开始插入记录")
    # 先过滤已存在的记录
    records_to_insert = batch_check_records_exist(records, att_date)
    print("过滤完成")
    if not records_to_insert:
        print("所有记录已存在，无需插入")
        return 0
    insert_sql = """
        INSERT INTO checkinout_dwd (
            USERID, CHECKTIME, CHECKTYPE, VERIFYCODE, SENSORID,
            MemoInfo, WorkCode, sn, UserExtFmt, mask_flag, temperature
        ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
    """
    params = [
        (record['user_id'], record['check_time'])
        for record in records_to_insert
    ]
    # 准备插入参数
    params_list = [
        (
            userid,
            checktime,
            'I', 15, None,  # CHECKTYPE, VERIFYCODE, SENSORID
            None, None, None, None,  # MemoInfo, WorkCode, sn, UserExtFmt
            None, 0  # mask_flag, temperature
        )
        for (userid, checktime) in params
    ]

    try:
        pytime.sleep(5)
        print(f"开始插入新记录")

        inserted_count = zymysql.zkteco_executemany(insert_sql, params_list)
        print(f"成功插入 {inserted_count} 条新记录")
        return inserted_count
    except Exception as e:
        print(f"批量插入出错: {e}")
        return 0


def delete_history_data(midnight_and_nextday, is_batch):
    zk_delete_sql = "delete from checkinout_dwd WHERE CHECKTIME BETWEEN  %s and %s"
    try:
        zymysql.zkteco_execute(zk_delete_sql, midnight_and_nextday)
    except Exception as e:
        print(f"删除中间库操作出错: {zk_delete_sql}, {midnight_and_nextday}, {e}")
        return False

    param = (int(midnight_and_nextday[0].timestamp() * 1000), int(midnight_and_nextday[1].timestamp() * 1000))
    if is_batch:
        seeyon_backup_sql = "INSERT INTO att_historyrecord_backup SELECT * FROM att_historyrecord WHERE sign_datetime > %s AND sign_datetime < %s AND source_type = 1"
        try:
            zymysql.seeyon_param_execute(seeyon_backup_sql, param)
        except Exception as e:
            print(f"备份操作出错: {seeyon_backup_sql},{param},{e}")
            return False

    seeyon_delete_sql = "delete from att_historyrecord where sign_datetime> %s and sign_datetime<%s and source_type=1"
    try:
        zymysql.seeyon_param_execute(seeyon_delete_sql, param)
    except Exception as e:
        print(f"删除oa数据操作出错: {seeyon_backup_sql},{param},{e}")
        return False


# is_batch是否批量处理
def start(att_date, is_batch):
    midnight_and_nextday = get_midnight_and_nextday(att_date)

    # 获取考勤排班数据
    att_date_list = get_att_time(att_date)
    # 获取考勤打卡数据
    check_time_list = get_att_check_in_out_time(midnight_and_nextday)
    # 剔除多班次间连续打卡数据（间隔十分钟）
    filter_records = filter_schedule_by_checkins(att_date_list, check_time_list)
    delete_history_data(midnight_and_nextday, is_batch)
    # 保存打卡数据
    batch_insert_attendance_records(filter_records, midnight_and_nextday)


def date_migrate(att_date):
    # 获取考勤打卡数据
    yesterday_and_midnight = get_yesterday_and_midnight(att_date)
    check_time_list = get_att_check_in_out_time(yesterday_and_midnight)
    # 保存打卡数据
    batch_insert_attendance_records(check_time_list, yesterday_and_midnight)


if __name__ == '__main__':
    yesterday = datetime(2025, 4, 16)
    start(yesterday)
